package com.apes.scm.invoice.repository;

import com.apes.framework.jpa.repository.CustomRepository;
import com.apes.scm.account.account.model.Cooperation;
import com.apes.scm.invoice.model.Invoice;
import com.apes.scm.invoice.model.InvoiceItem;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * @author xul
 * @create 2018-01-23 16:07
 */
public interface InvoiceRepository extends CustomRepository<Invoice, Long> {

    Invoice findBySourceBusinessTypeAndSource(String sourceBusinessType, String source);


    /**
     * fetch data by partner Id and contract type Id and start date and end Date
     *
     * @param startDate          start date
     * @param endDate            end Date
     * @param companyId          company id
     * @param originBusinessType company id
     * @return List<Invoice>
     */
    @Query(
            value = "select t " +
                    "from Invoice t " +
                    "where t.company.id = ?1 and t.originBusinessType=?2  and t.createDate >= ?3 and t.createDate<?4 "
    )
    List<Invoice> findAllByCompanyAndOriginBusinessType(String companyId, String originBusinessType, Date startDate, Date endDate);

    /**
     * fetch data by partner Id and contract type Id and start date and end Date
     *
     * @param startDate          start date
     * @param endDate            end Date
     * @param companyId          company id
     * @param originBusinessType company id
     * @param pageable           pageable
     * @return Page<Invoice>
     */
    @Query(
            value = "select t " +
                    "from Invoice t " +
                    "where t.company.id = ?1 and t.originBusinessType=?2  and t.createDate >= ?3 and t.createDate<?4 "
    )
    Page<Invoice> findAllByCompanyAndOriginBusinessType(String companyId, String originBusinessType, Date startDate, Date endDate, Pageable pageable);

    /**
     * 根据公司和客户查找最早一单未结算单据
     *
     * @param companyId
     * @param partnerId
     * @return
     */
    @Query(value = "SELECT *" +
            "  FROM (SELECT DISTINCT A.*" +
            "           FROM ACC_INVOICE A, ACC_INVOICE_ITEM B, MAS_PARTY_ROLE C" +
            "          WHERE A.ID = B.INVOICE_ID" +
            "            AND A.PARTNER_ID = C.ID" +
            "            AND C.ROLE_ID = 'Customer'" +
            "            AND A.COMPANY_ID = :companyId" +
            "            AND A.PARTNER_ID = :partnerId" +
            "            AND (B.FREEZE_QTY > 0 OR B.SETTLED_QTY = 0)" +
            "          ORDER BY A.CREATE_DATE ASC)" +
            " WHERE ROWNUM = 1", nativeQuery = true)
    Invoice findFirstByCompanyIdAndPartnerId(String companyId, String partnerId);


    @Query(value = "SELECT '[' || b.product_id || ']' || (SELECT M.NAME FROM MAS_PRODUCT M WHERE b.PRODUCT_ID = \n" +
            "M.ID) AS PRODUCT_NAME,case when a.COOPERATION_ID = 'distribution' then '经销' else '代销' end INVENTORYMODE,\n" +
            "b.change_balance_qty PRODUCT_QTY, b.old_price_unit PRICE_UNIT, b.amt_with_tax amount, to_char(a.create_date, 'yyyy-mm-dd') \n" +
            "create_date, '[' || (SELECT M.Classify_Id FROM MAS_PRODUCT M WHERE b.PRODUCT_ID = M.ID) || \n" +
            "']' || (SELECT z.NAME FROM MAS_PRODUCT M, mas_classify z WHERE m.classify_id = z.id and b.PRODUCT_ID \n" +
            "= M.ID) classify,  a.source, c.source_business_type, C.CAR_LICENSE \n" +
            "CAR_LICENSE FROM pur_price_change a, pur_price_change_item b LEFT JOIN ACC_VOUCHER_EXTENSION C ON B.ID \n" +
            "= C.ID where a.id = b.price_change_id and b.product_id = :productId" +
            "    and a.company_id = :company and a.cooperation_id=:inventory and to_char(a.create_date,'yyyy-mm') = substr(:createDate,0,7) \n", nativeQuery = true)
    List<Map> findPriceChangeMonth(String productId, String company, String createDate,String inventory);

    @Query(value = "SELECT '[' || b.product_id || ']' || (SELECT M.NAME FROM MAS_PRODUCT M WHERE b.PRODUCT_ID = \n" +
            "M.ID) AS PRODUCT_NAME,case when a.COOPERATION_ID = 'distribution' then '经销' else '代销' end INVENTORYMODE,\n" +
            "b.change_balance_qty PRODUCT_QTY, b.old_price_unit PRICE_UNIT, b.amt_with_tax amount, to_char(a.create_date, 'yyyy-mm-dd') \n" +
            "create_date, '[' || (SELECT M.Classify_Id FROM MAS_PRODUCT M WHERE b.PRODUCT_ID = M.ID) || \n" +
            "']' || (SELECT z.NAME FROM MAS_PRODUCT M, mas_classify z WHERE m.classify_id = z.id and b.PRODUCT_ID \n" +
            "= M.ID) classify,  a.source, c.source_business_type, C.CAR_LICENSE \n" +
            "CAR_LICENSE FROM pur_price_change a, pur_price_change_item b LEFT JOIN ACC_VOUCHER_EXTENSION C ON B.ID \n" +
            "= C.ID where a.id = b.price_change_id and b.product_id = :productId" +
            "   and a.company_id = :company and a.cooperation_id=:inventory and a.create_date > :createDate and a.create_date <= :createDate+1\n", nativeQuery = true)
    List<Map> findPriceChange(String productId, String company, Date createDate,String inventory);

    @Query(value = "SELECT '[' || a.product_id || ']' || (SELECT M.NAME FROM MAS_PRODUCT M WHERE a.PRODUCT_ID = M.ID) AS product_name,\n" +
            "       '[' || (SELECT m.classify_id FROM MAS_PRODUCT M WHERE a.PRODUCT_ID = M.ID) || ']' ||\n" +
            "        (SELECT b.NAME FROM MAS_PRODUCT M, mas_classify b WHERE b.id = m.classify_id and a.PRODUCT_ID = M.ID) AS CLASSIFY,\n" +
            "       case when a.COOPERATION_ID = 'distribution' then '经销' else '代销' end INVENTORYMODE, a.BALANCE_QTY PRODUCT_QTY,\n" +
            "       (SELECT b.tax_cost_price FROM acc_inventory_batch b where a.inventory_id = b.id) PRICE_UNIT,\n" +
            "      (SELECT b.tax_cost_price FROM acc_inventory_batch b where a.inventory_id = b.id) amount,\n" +
            "       to_char(a.create_date, 'yyyy-mm-dd') CREATE_DATE,\n" +
            "       '[' || A.COMPANY_ID || ']' || (SELECT C.NAME FROM MAS_PARTY_ROLE B, MAS_PARTY C WHERE A.COMPANY_ID = B.ID\n" +
            "        AND B.PARTY_ID = C.ID) COMPANY_NAME FROM ACC_INVENTORY_BATCH_DAY a where a.PRODUCT_ID = :productId\n" +
            "   and a.COOPERATION_ID = :inventory and a.company_id = :company" +
            "   and a.create_date > :createDate-1 and a.create_date <= :createDate order by a.create_date desc\n", nativeQuery = true)
    List<Map> findBalance(String productId, String inventory, String company, Date createDate);

    @Query(value = "SELECT '[' || a.product_id || ']' || (SELECT M.NAME FROM MAS_PRODUCT M WHERE a.PRODUCT_ID = M.ID) AS product_name,\n" +
            "                   '[' || (SELECT m.classify_id FROM MAS_PRODUCT M WHERE a.PRODUCT_ID = M.ID) || ']' ||\n" +
            "                    (SELECT b.NAME FROM MAS_PRODUCT M, mas_classify b WHERE b.id = m.classify_id and a.PRODUCT_ID = M.ID) AS CLASSIFY,\n" +
            "                   case when a.COOPERATION_ID = 'distribution' then '经销' else '代销' end INVENTORYMODE, a.BALANCE_QTY PRODUCT_QTY,\n" +
            "                   (SELECT b.tax_cost_price FROM acc_inventory_batch b where a.inventory_id = b.id) PRICE_UNIT,\n" +
            "                   (SELECT b.tax_cost_price FROM acc_inventory_batch b where a.inventory_id = b.id) amount,\n" +
            "                   a.month CREATE_DATE, \n" +
            "                   '[' || A.COMPANY_ID || ']' || (SELECT C.NAME FROM MAS_PARTY_ROLE B, MAS_PARTY C WHERE A.COMPANY_ID = B.ID\n" +
            "                    AND B.PARTY_ID = C.ID) COMPANY_NAME FROM ACC_INVENTORY_BATCH_MONTH a where a.PRODUCT_ID = :productId\n" +
            "               and a.COOPERATION_ID = :inventory and a.company_id = :company\n" +
            "               and  a.month = :createDate order by a.month desc", nativeQuery = true)
    List<Map> findBalanceMonth(String productId, String inventory, String company, String createDate);


    @Query(value = "SELECT '[' || a.product_id || ']' || (SELECT M.NAME FROM MAS_PRODUCT M WHERE a.PRODUCT_ID = M.ID) AS product_name,\n" +
            "       '[' || (SELECT m.classify_id FROM MAS_PRODUCT M WHERE a.PRODUCT_ID = M.ID) || ']' ||\n" +
            "        (SELECT b.NAME FROM MAS_PRODUCT M, mas_classify b WHERE b.id = m.classify_id and a.PRODUCT_ID = M.ID) AS CLASSIFY,\n" +
            "       case when a.COOPERATION_ID = 'distribution' then '经销' else '代销' end INVENTORYMODE, a.BALANCE_QTY PRODUCT_QTY,\n" +
            "       (SELECT b.tax_cost_price FROM acc_inventory_batch b where a.inventory_id = b.id) PRICE_UNIT,\n" +
            "      (SELECT b.tax_cost_price FROM acc_inventory_batch b where a.inventory_id = b.id) amount,\n" +
            "       to_char(a.create_date, 'yyyy-mm-dd') CREATE_DATE,\n" +
            "       '[' || A.COMPANY_ID || ']' || (SELECT C.NAME FROM MAS_PARTY_ROLE B, MAS_PARTY C WHERE A.COMPANY_ID = B.ID\n" +
            "        AND B.PARTY_ID = C.ID) COMPANY_NAME FROM ACC_INVENTORY_BATCH_DAY a where a.PRODUCT_ID = :productId\n" +
            "   and a.COOPERATION_ID = :inventory and a.company_id = :company" +
            "   and a.create_date >= :createDate-1 and a.create_date < :createDate order by a.create_date desc\n", nativeQuery = true)
    List<Map> findBeginning(String productId, String inventory, String company, Date createDate);

    @Query(value = "SELECT '[' || a.product_id || ']' || (SELECT M.NAME FROM MAS_PRODUCT M WHERE a.PRODUCT_ID = M.ID) AS product_name,\n" +
            "                   '[' || (SELECT m.classify_id FROM MAS_PRODUCT M WHERE a.PRODUCT_ID = M.ID) || ']' ||\n" +
            "                    (SELECT b.NAME FROM MAS_PRODUCT M, mas_classify b WHERE b.id = m.classify_id and a.PRODUCT_ID = M.ID) AS CLASSIFY,\n" +
            "                   case when a.COOPERATION_ID = 'distribution' then '经销' else '代销' end INVENTORYMODE, a.BALANCE_QTY PRODUCT_QTY,\n" +
            "                   (SELECT b.tax_cost_price FROM acc_inventory_batch b where a.inventory_id = b.id) PRICE_UNIT,\n" +
            "                   (SELECT b.tax_cost_price FROM acc_inventory_batch b where a.inventory_id = b.id) amount,\n" +
            "                   a.month CREATE_DATE, \n" +
            "                   '[' || A.COMPANY_ID || ']' || (SELECT C.NAME FROM MAS_PARTY_ROLE B, MAS_PARTY C WHERE A.COMPANY_ID = B.ID\n" +
            "                    AND B.PARTY_ID = C.ID) COMPANY_NAME FROM ACC_INVENTORY_BATCH_MONTH a where a.PRODUCT_ID = :productId\n" +
            "               and a.COOPERATION_ID = :inventory and a.company_id = :company\n" +
            "               and  a.month = :createDate order by a.month desc", nativeQuery = true)
    List<Map> findBeginningMonth(String productId, String inventory, String company, String createDate);

    @Query(value = "SELECT A.* FROM ACC_INVOICE A WHERE A.state = 0 AND A.ORIGIN_BUSINESS_TYPE <> 'ProfitAndLoss' AND A.COMPANY_ID = :company AND A.PARTNER_ID = :partner " +
            "AND A.INVENTORY_MODE = :inventoryMode AND TRUNC(A.THIRD_TIME) >= TRUNC(:startDate) AND TRUNC(A.THIRD_TIME) <= TRUNC(:endDate)",nativeQuery = true)
    List<Invoice> findBySupplierContract(@Param("company") String company,@Param("partner") String partner,
                           @Param("inventoryMode") String inventoryMode,@Param("startDate") Date startDate,@Param("endDate") Date endDate);

    @Query(value = "SELECT A.* FROM ACC_INVOICE A,FN_CONTRACT B WHERE A.state = 0 AND A.COMPANY_ID = B.PARTNER_ID AND B.state = 'approve' AND A.ORIGIN_BUSINESS_TYPE <> 'ProfitAndLoss' AND TRUNC(B.VALID_PERIOD) >= TRUNC(:endDate) AND TRUNC(B.VALID_FROM) <= TRUNC(:endDate) AND B.COMPANY_ID = :company AND A.PARTNER_ID = :partner " +
            "AND A.INVENTORY_MODE = :inventoryMode AND TRUNC(A.THIRD_TIME) >= TRUNC(:startDate) AND TRUNC(A.THIRD_TIME) <= TRUNC(:endDate)",nativeQuery = true)
    List<Invoice> findByPlatformContract(@Param("company") String company,@Param("partner") String partner,
                                         @Param("inventoryMode") String inventoryMode,@Param("startDate") Date startDate,@Param("endDate") Date endDate);

    @Query(value = "SELECT DISTINCT A.ID id FROM PLATFORM_SETTLE A,FN_PLATFORM_SETTLE_ITEM B,ACC_INVOICE_ITEM C WHERE A.ID = B.PLATFORM_SETTLE_ID AND A.STATE IN ('sent','approve') " +
            "AND B.INVOICE_ID = C.ID AND C.INVOICE_ID IN (SELECT T.ID FROM ACC_INVOICE T WHERE T.THIRD_TIME >= :startDate AND T.THIRD_TIME <= :endDate AND T.PARTNER_ID = :supplierId)",nativeQuery = true)
    List<Map> findPlatform(@Param("startDate") Date startDate,@Param("endDate") Date endDate,@Param("supplierId") String supplierId);
}
